package com.showde.shoppingmall.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.kim.androidbase.db.DBCommandInteface;
import com.kim.androidbase.db.DBSession;
import com.kim.androidbase.db.SQLiteCriteria;
import com.showde.shoppingmall.db.constant.ProductConstant;
import com.showde.shoppingmall.db.constant.ProductDescConstant;
import com.showde.shoppingmall.db.constant.ShoppingCartConstant;
import com.showde.shoppingmall.domain.Product;
import com.showde.shoppingmall.domain.ProductDesc;
import com.showde.shoppingmall.domain.ShoppingCart;

public class ShoppingcartDBCommand implements
		DBCommandInteface<ShoppingCart, String> {

	DBSession dbSession;

	public ShoppingcartDBCommand(Context context) {
		dbSession = new DBSession(context, new DBHelper(context));
	}

	@Override
	public void add(ShoppingCart t) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(ShoppingCartConstant.PRODUCTID, t.getProductId());
		contentValues.put(ShoppingCartConstant.PRODUCTCOUNT, 1);
		contentValues.put(ShoppingCartConstant.PRODUCTTIME,
				new Date().getTime());
		dbSession.insert(ShoppingCartConstant.TABLENAME, contentValues,
				SQLiteDatabase.CONFLICT_ABORT);
		contentValues = null;
	}

	@Override
	public void add(List<ShoppingCart> ts) {

	}

	@Override
	public void delete(String pk) {
		dbSession.delete(ShoppingCartConstant.TABLENAME,
				ShoppingCartConstant.PRODUCTID + "=?", new String[] { pk });
	}

	@Override
	public void delete(SQLiteCriteria sqLiteCriteria) {

	}

	@Override
	public void modify(SQLiteCriteria sqLiteCriteria) {

	}

	@Override
	public void modify(String pk, SQLiteCriteria sqLiteCriteria) {
		// TODO Auto-generated method stub

	}

	public void updateShoppingcartCount(String pk, int count) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(ShoppingCartConstant.PRODUCTCOUNT, count);
		dbSession.modify(ShoppingCartConstant.TABLENAME, contentValues,
				ShoppingCartConstant.PRODUCTID + "=?", new String[] { pk });
		contentValues = null;
	}

	@Override
	public ShoppingCart query(String pk) {
		return null;
	}

	@Override
	public List<ShoppingCart> query(SQLiteCriteria sqLiteCriteria) {
		// TODO Auto-generated method stub
		return null;
	}

	public List<ShoppingCart> queryShoppingcartByProductIdArray(
			List<String> productIds) {

		if (productIds == null || productIds.size() == 0) {
			return null;
		}

		StringBuilder builder = new StringBuilder(productIds.size());
		for (String id : productIds) {
			builder.append("'" + id + "',");
		}
		String idsStr = builder.toString();
		idsStr = idsStr.substring(0, idsStr.length() - 1);
		String ids = "(" + idsStr + ")";

		String sql = "select p." + ProductDescConstant.PRODUCTDESCID
				+ " id, p." + ProductDescConstant.PRODUCTDESCNAME + " name, p."
				+ ProductDescConstant.PRODUCTDESCTITLE + " title, p."
				+ ProductDescConstant.PRODUCTDESCLOGO + " logo, p."
				+ ProductDescConstant.PRODUCTDESCPRICE + " price, s."
				+ ShoppingCartConstant.PRODUCTCOUNT + " scount from "
				+ ProductDescConstant.TABLENAME + " p,"
				+ ShoppingCartConstant.TABLENAME + " s where p."
				+ ProductDescConstant.PRODUCTDESCID + "=s."
				+ ShoppingCartConstant.PRODUCTID + " and s."
				+ ShoppingCartConstant.PRODUCTID + " in " + ids + " order by "
				+ ShoppingCartConstant.PRODUCTTIME + " desc";

		Cursor cursor = null;
		List<ShoppingCart> shoppingCarts = null;
		try {
			cursor = dbSession.rawQuery(sql, null);
			shoppingCarts = cursor2Shoppingcart(cursor);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return shoppingCarts;
	}

	@Override
	public List<ShoppingCart> queryAll() {
		String sql = "select p." + ProductDescConstant.PRODUCTDESCID
				+ " id, p." + ProductDescConstant.PRODUCTDESCNAME + " name, p."
				+ ProductDescConstant.PRODUCTDESCTITLE + " title, p."
				+ ProductDescConstant.PRODUCTDESCLOGO + " logo, p."
				+ ProductDescConstant.PRODUCTDESCPRICE + " price, s."
				+ ShoppingCartConstant.PRODUCTCOUNT + " scount from "
				+ ProductDescConstant.TABLENAME + " p,"
				+ ShoppingCartConstant.TABLENAME + " s where p."
				+ ProductDescConstant.PRODUCTDESCID + "=s."
				+ ShoppingCartConstant.PRODUCTID + " order by "
				+ ShoppingCartConstant.PRODUCTTIME + " desc";
		Cursor cursor = null;
		List<ShoppingCart> shoppingCarts = null;
		try {
			cursor = dbSession.rawQuery(sql, null);
			shoppingCarts = cursor2Shoppingcart(cursor);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return shoppingCarts;
	}

	public int getShoppingcartCount() {
		Cursor cursor = null;
		String sql = "select count(*) countAll from "
				+ ShoppingCartConstant.TABLENAME;
		try {
			cursor = dbSession.rawQuery(sql, null);
			if (cursor != null && cursor.moveToNext()) {
				return dbSession.getInt(cursor, "countAll");
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return 0;
	}

	public float getShoppingcarTotalFee() {
		Cursor cursor = null;
		String sql = "select sum(" + ShoppingCartConstant.PRODUCTCOUNT + "*"
				+ ProductConstant.PRODUCTPRICE + ") countAll from "
				+ ShoppingCartConstant.TABLENAME + " sc,"
				+ ProductConstant.TABLENAME + " p where p."
				+ ProductConstant.PRODUCTID + " = sc."
				+ ShoppingCartConstant.PRODUCTID;
		try {
			cursor = dbSession.rawQuery(sql, null);
			if (cursor != null && cursor.moveToNext()) {
				return dbSession.getFloat(cursor, "countAll");
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return 0;
	}

	public boolean exist(String productId) {
		Cursor cursor = null;
		String sql = "select count(*) existP from "
				+ ShoppingCartConstant.TABLENAME + " where "
				+ ShoppingCartConstant.PRODUCTID + "=?";
		try {
			cursor = dbSession.rawQuery(sql, new String[] { productId });
			if (cursor != null && cursor.moveToNext()) {
				return dbSession.getBoolean(cursor, "existP");
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}
		return false;
	}

	private List<ShoppingCart> cursor2Shoppingcart(Cursor cursor) {
		if (cursor != null && cursor.getCount() > 0) {
			List<ShoppingCart> shoppingCarts = new ArrayList<ShoppingCart>();
			ShoppingCart shoppingCart = null;
			while (cursor.moveToNext()) {
				shoppingCart = new ShoppingCart();
				shoppingCart.setProductId(dbSession.getString(cursor, "id"));
				shoppingCart
						.setProductName(dbSession.getString(cursor, "name"));
				shoppingCart.setProductTitle(dbSession.getString(cursor,
						"title"));
				shoppingCart
						.setProductLogo(dbSession.getString(cursor, "logo"));
				shoppingCart.setProductPrice(dbSession
						.getFloat(cursor, "price"));
				shoppingCart
						.setProductCount(dbSession.getInt(cursor, "scount"));
				shoppingCarts.add(shoppingCart);
			}
			return shoppingCarts;
		}
		return null;
	}

}
